package birs.controller.staff.task;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import birs.model.staff.ChartModel;
import birs.model.staff.HeaderDialog;
import birs.model.staff.StaffType;
import birs.model.staff.StaffTypeByType;
import birs.model.staff.VHrmStaffEduHighest;
import birs.util.ConvertDataType;
import birs.util.ResultSetMapper;

public class STF001Task {
	
	Connection con = null;
	
	public STF001Task(Connection con){
		this.con = con;
	}
	
	public void getDrilldown(HttpServletRequest request,HttpServletResponse response) throws IOException {

		String staffType;
		HeaderDialog headerDialog = new HeaderDialog();
		if(request.getParameter("p1").equals("drilldownBySex")){
			if(request.getParameter("p2").equals("male")){
				if(request.getParameter("p3").equals("gt")){
					staffType = "GTMALE = 1";
					headerDialog.setName("ข้าราชการสายวิชาการ เพศชาย");
				}
				else if(request.getParameter("p3").equals("gs")){
					staffType = "GSMALE = 1";
					headerDialog.setName("ข้าราชการสายสนับสนุน เพศชาย");
				}
				else if(request.getParameter("p3").equals("emp")){
					staffType = "EMPMALE = 1";
					headerDialog.setName("ลูกจ้างประจำ เพศชาย");
				}
				else if(request.getParameter("p3").equals("empg")){
					staffType = "EMPGMALE = 1";
					headerDialog.setName("พนักงานราชการ เพศชาย");
				}
				else if(request.getParameter("p3").equals("ts")){
					staffType = "TSMALE = 1";
					headerDialog.setName("พนักงานมหาวิทยาลัยสายวิชาการ เพศชาย");
				}
				else if(request.getParameter("p3").equals("u")){
					staffType = "UMALE = 1";
					headerDialog.setName("พนักงานมหาวิทยาลัยสายสนับสนุน เพศชาย");
				}
				else{
					staffType = "TMP1MALE = 1 OR TMP2MALE = 1";
					headerDialog.setName("ลูกจ้างชั่วคราว เพศชาย");
				}
			}
			else{
				if(request.getParameter("p3").equals("gt")){
					staffType = "GTFEMALE = 1";
					headerDialog.setName("ข้าราชการสายวิชาการ เพศหญิง");
				}
				else if(request.getParameter("p3").equals("gs")){
					staffType = "GSFEMALE = 1";
					headerDialog.setName("ข้าราชการสายสนับสนุน เพศหญิง");
				}
				else if(request.getParameter("p3").equals("emp")){
					staffType = "EMPFEMALE = 1";
					headerDialog.setName("ลูกจ้างประจำ เพศหญิง");
				}
				else if(request.getParameter("p3").equals("empg")){
					staffType = "EMPGFEMALE = 1";
					headerDialog.setName("พนักงานราชการ เพศหญิง");
				}
				else if(request.getParameter("p3").equals("ts")){
					staffType = "TSFEMALE = 1";
					headerDialog.setName("พนักงานมหาวิทยาลัยสายวิชาการ เพศหญิง");
				}
				else if(request.getParameter("p3").equals("u")){
					staffType = "UFEMALE = 1";
					headerDialog.setName("พนักงานมหาวิทยาลัยสายสนับสนุน เพศหญิง");
				}
				else{
					staffType = "TMP1FEMALE = 1 OR TMP2FEMALE = 1";
					headerDialog.setName("ลูกจ้างชั่วคราว เพศหญิง");
				}
			}
			ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduHDetailDrilldown = new ResultSetMapper<VHrmStaffEduHighest>();
			List<VHrmStaffEduHighest> vHrmStaffEduHighestDetailDrilldownList = vHrmStaffEduHDetailDrilldown.mapRersultSetToObject(getDataDrilldown(staffType), VHrmStaffEduHighest.class);
			List<StaffTypeByType> drilldownStaffList = new ArrayList<StaffTypeByType>();
			int seq = 0;
			for (VHrmStaffEduHighest vHrmStaffEdu : vHrmStaffEduHighestDetailDrilldownList) {
				StaffTypeByType drilldownStaff = new StaffTypeByType();
				drilldownStaff.setSequence(++seq);
				drilldownStaff.setName(vHrmStaffEdu.getPrefixName()+vHrmStaffEdu.getStaffName()+" "+vHrmStaffEdu.getStaffSurname());
				drilldownStaff.setDepName(vHrmStaffEdu.getDepartmentName());
				drilldownStaffList.add(drilldownStaff);
			}
			List<Object> dialogList = new ArrayList<Object>();
			dialogList.add(headerDialog);
			dialogList.add(drilldownStaffList);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(dialogList);
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/json;charset=utf-8");
			PrintWriter out = response.getWriter();
			out.write(resultJson);
			out.close();
		}
		else{
			if(request.getParameter("p3").equals("gt")){
				staffType = "GTMALE = 1 OR GTFEMALE = 1";
				headerDialog.setName("ข้าราชการสายวิชาการ");
			}
			else if(request.getParameter("p3").equals("gs")){
				staffType = "GSMALE = 1 OR GSFEMALE = 1";
				headerDialog.setName("ข้าราชการสายสนับสนุน");
			}
			else if(request.getParameter("p3").equals("emp")){
				staffType = "EMPMALE = 1 OR EMPFEMALE = 1";
				headerDialog.setName("ลูกจ้างประจำ");
			}
			else if(request.getParameter("p3").equals("empg")){
				staffType = "EMPGMALE = 1 OR EMPGFEMALE = 1";
				headerDialog.setName("พนักงานราชการ");
			}
			else if(request.getParameter("p3").equals("ts")){
				staffType = "TSMALE = 1 OR TSFEMALE = 1";
				headerDialog.setName("พนักงานมหาวิทยาลัยสายวิชาการ");
			}
			else if(request.getParameter("p3").equals("u")){
				staffType = "UMALE = 1 OR UFEMALE = 1";
				headerDialog.setName("พนักงานมหาวิทยาลัยสายสนับสนุน");
			}
			else {
				staffType = "TMP1MALE = 1 OR TMP2MALE = 1 OR TMP1FEMALE = 1 OR TMP2FEMALE = 1";
				headerDialog.setName("ลูกจ้างชั่วคราว");
			}
			ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduHDetailDrilldown = new ResultSetMapper<VHrmStaffEduHighest>();
			List<VHrmStaffEduHighest> vHrmStaffEduHighestDetailDrilldownList = vHrmStaffEduHDetailDrilldown.mapRersultSetToObject(getDataDrilldown(staffType), VHrmStaffEduHighest.class);
			List<StaffTypeByType> drilldownStaffList = new ArrayList<StaffTypeByType>();
			int seq = 0;
			ChartModel maleChart = new ChartModel();
			maleChart.setLabel("ชาย");
			ChartModel femaleChart = new ChartModel();
			femaleChart.setLabel("หญิง");
			for (VHrmStaffEduHighest vHrmStaffEdu : vHrmStaffEduHighestDetailDrilldownList) {
				StaffTypeByType drilldownStaff = new StaffTypeByType();
				drilldownStaff.setSequence(++seq);
				drilldownStaff.setName(vHrmStaffEdu.getPrefixName()+vHrmStaffEdu.getStaffName()+" "+vHrmStaffEdu.getStaffSurname());
				drilldownStaff.setDepName(vHrmStaffEdu.getDepartmentName());
				drilldownStaffList.add(drilldownStaff);
				if(vHrmStaffEdu.getGtMale()==1||vHrmStaffEdu.getGsMale()==1||vHrmStaffEdu.getEmpMale()==1||vHrmStaffEdu.getEmpGMale()==1||vHrmStaffEdu.getTsMale()==1
						||vHrmStaffEdu.getuMale()==1||vHrmStaffEdu.getTmpAMale()==1||vHrmStaffEdu.getTmpSMale()==1){
					maleChart.setValue(maleChart.getValue()+1);
				}else{
					femaleChart.setValue(femaleChart.getValue()+1);
				}
			}
			List<Object> drilldownData = new ArrayList<Object>();
			List<Object> donutChart = new ArrayList<Object>();
			donutChart.add(maleChart);
			donutChart.add(femaleChart);
			drilldownData.add(headerDialog);
			drilldownData.add(drilldownStaffList);
			drilldownData.add(donutChart);
			String resultJson = ConvertDataType.getInstance().objectToJasonArray(drilldownData);
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/json;charset=utf-8");
			PrintWriter out = response.getWriter();
			out.write(resultJson);
			out.close();
			
		}
	}
	
	public void getStaffType(HttpServletRequest request,HttpServletResponse response) throws IOException{

		ResultSetMapper<VHrmStaffEduHighest> vHrmStaffEduHighest = new ResultSetMapper<VHrmStaffEduHighest>();
		List<VHrmStaffEduHighest> vHrmStaffEduHighestList = vHrmStaffEduHighest.mapRersultSetToObject(getVHrmStaffEduHighestData(), VHrmStaffEduHighest.class);
		
		StaffType summaryShowGtStaff = new StaffType();
		StaffType summaryShowGsStaff = new StaffType();
		StaffType summaryShowEmpStaff = new StaffType();
		StaffType summaryShowEmpGStaff = new StaffType();
		StaffType summaryShowTsStaff = new StaffType();
		StaffType summaryShowUStaff = new StaffType();
		StaffType summaryShowTmpStaff = new StaffType();
		for (VHrmStaffEduHighest vHrmStaff : vHrmStaffEduHighestList) {
			
			if(vHrmStaff.getGtMale() == 1){
				summaryShowGtStaff.setMaleStaffAmount(summaryShowGtStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getGtFemale()==1){
				summaryShowGtStaff.setFemaleStaffAmount(summaryShowGtStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getGsMale()==1){
				summaryShowGsStaff.setMaleStaffAmount(summaryShowGsStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getGsFemale()==1){
				summaryShowGsStaff.setFemaleStaffAmount(summaryShowGsStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getEmpMale()==1){
				summaryShowEmpStaff.setMaleStaffAmount(summaryShowEmpStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getEmpFemale()==1){
				summaryShowEmpStaff.setFemaleStaffAmount(summaryShowEmpStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getEmpGMale()==1){
				summaryShowEmpGStaff.setMaleStaffAmount(summaryShowEmpGStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getEmpGFemale()==1){
				summaryShowEmpGStaff.setFemaleStaffAmount(summaryShowEmpGStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getTsMale()==1){
				summaryShowTsStaff.setMaleStaffAmount(summaryShowTsStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getTsFemale()==1){
				summaryShowTsStaff.setFemaleStaffAmount(summaryShowTsStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getuMale()==1){
				summaryShowUStaff.setMaleStaffAmount(summaryShowUStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getuFemale()==1){
				summaryShowUStaff.setFemaleStaffAmount(summaryShowUStaff.getFemaleStaffAmount()+1);
			}
			
			else if(vHrmStaff.getTmpAMale()==1 || vHrmStaff.getTmpSMale()==1){
				summaryShowTmpStaff.setMaleStaffAmount(summaryShowTmpStaff.getMaleStaffAmount()+1);
			}else if(vHrmStaff.getTmpAFemale()==1 || vHrmStaff.getTmpSFemale()==1){
				summaryShowTmpStaff.setFemaleStaffAmount(summaryShowTmpStaff.getFemaleStaffAmount()+1);
			}
			
		}
		

		int totalStaff = (summaryShowGtStaff.getMaleStaffAmount()+summaryShowGtStaff.getFemaleStaffAmount()) 
						+ (summaryShowGsStaff.getMaleStaffAmount()+summaryShowGsStaff.getFemaleStaffAmount()) 
						+ (summaryShowEmpStaff.getMaleStaffAmount()+summaryShowEmpStaff.getFemaleStaffAmount())
						+ (summaryShowEmpGStaff.getMaleStaffAmount()+summaryShowEmpGStaff.getFemaleStaffAmount())
						+ (summaryShowTsStaff.getMaleStaffAmount()+summaryShowTsStaff.getFemaleStaffAmount())
						+ (summaryShowUStaff.getMaleStaffAmount()+summaryShowUStaff.getFemaleStaffAmount())
						+ (summaryShowTmpStaff.getMaleStaffAmount()+summaryShowTmpStaff.getFemaleStaffAmount());
		
		summaryShowGtStaff.setPercenttage(getPercenttage(summaryShowGtStaff.getMaleStaffAmount()+summaryShowGtStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowGtStaff.setSequence(1);
		summaryShowGtStaff.setStaffType("ข้าราชการสายวิชาการ");
		summaryShowGtStaff.setStaffTypeCode("gt");
		summaryShowGtStaff.setTotalStaff(summaryShowGtStaff.getMaleStaffAmount()+summaryShowGtStaff.getFemaleStaffAmount());
		
		summaryShowGsStaff.setPercenttage(getPercenttage(summaryShowGsStaff.getMaleStaffAmount()+summaryShowGsStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowGsStaff.setSequence(2);
		summaryShowGsStaff.setStaffType("ข้าราชการสายสนับสนุน");
		summaryShowGsStaff.setStaffTypeCode("gs");
		summaryShowGsStaff.setTotalStaff(summaryShowGsStaff.getMaleStaffAmount()+summaryShowGsStaff.getFemaleStaffAmount());
		
		summaryShowEmpStaff.setPercenttage(getPercenttage(summaryShowEmpStaff.getMaleStaffAmount()+summaryShowEmpStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowEmpStaff.setSequence(3);
		summaryShowEmpStaff.setStaffType("ลูกจ้างประจำ");
		summaryShowEmpStaff.setStaffTypeCode("emp");
		summaryShowEmpStaff.setTotalStaff(summaryShowEmpStaff.getMaleStaffAmount()+summaryShowEmpStaff.getFemaleStaffAmount());
		
		summaryShowEmpGStaff.setPercenttage(getPercenttage(summaryShowEmpGStaff.getMaleStaffAmount()+summaryShowEmpGStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowEmpGStaff.setSequence(4);
		summaryShowEmpGStaff.setStaffType("พนักงานราชการ");
		summaryShowEmpGStaff.setStaffTypeCode("empg");
		summaryShowEmpGStaff.setTotalStaff(summaryShowEmpGStaff.getMaleStaffAmount()+summaryShowEmpGStaff.getFemaleStaffAmount());
		
		
		summaryShowTsStaff.setPercenttage(getPercenttage(summaryShowTsStaff.getMaleStaffAmount()+summaryShowTsStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowTsStaff.setSequence(5);
		summaryShowTsStaff.setStaffType("พนักงานมหาวิทยาลัยสายวิชาการ");
		summaryShowTsStaff.setStaffTypeCode("ts");
		summaryShowTsStaff.setTotalStaff(summaryShowTsStaff.getMaleStaffAmount()+summaryShowTsStaff.getFemaleStaffAmount());
		
		summaryShowUStaff.setPercenttage(getPercenttage(summaryShowUStaff.getMaleStaffAmount()+summaryShowUStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowUStaff.setSequence(6);
		summaryShowUStaff.setStaffType("พนักงานมหาวิทยาลัยสายสนับสนุน");
		summaryShowUStaff.setStaffTypeCode("u");
		summaryShowUStaff.setTotalStaff(summaryShowUStaff.getMaleStaffAmount()+summaryShowUStaff.getFemaleStaffAmount());
		
		summaryShowTmpStaff.setPercenttage(getPercenttage(summaryShowTmpStaff.getMaleStaffAmount()+summaryShowTmpStaff.getFemaleStaffAmount(), totalStaff));
		summaryShowTmpStaff.setSequence(7);
		summaryShowTmpStaff.setStaffType("ลูกจ้างชั่วคราว");
		summaryShowTmpStaff.setStaffTypeCode("tmp");
		summaryShowTmpStaff.setTotalStaff(summaryShowTmpStaff.getMaleStaffAmount()+summaryShowTmpStaff.getFemaleStaffAmount());
		
		List<StaffType> tBody = new ArrayList<StaffType>();
		tBody.add(summaryShowGtStaff);
		tBody.add(summaryShowGsStaff);
		tBody.add(summaryShowEmpStaff);
		tBody.add(summaryShowEmpGStaff);
		tBody.add(summaryShowTsStaff);
		tBody.add(summaryShowUStaff);
		tBody.add(summaryShowTmpStaff);
		
		String resultJson = ConvertDataType.getInstance().objectToJasonArray(tBody);
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/json;charset=utf-8");
		PrintWriter out = response.getWriter();
		out.write(resultJson);
		out.close();
	}
	
	private ResultSet getVHrmStaffEduHighestData(){
		String sql = "SELECT * FROM V_HRM_STAFFEDU_HIGHEST";
		return getData(sql);
	}
	
	private ResultSet getDataDrilldown(String p){
		String sql = "SELECT * FROM V_HRM_STAFFEDU_HIGHEST WHERE "+p;
		return getData(sql);
	}
	
	private ResultSet getData(String sql){
		ResultSet result = null;
		try {
			//ConnectionDB.getInstance();
			//con = ConnectionDB.getMisConnection();
			Statement statement = con.createStatement();
			result = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	
	private double getPercenttage(int amountStaffbyType,int totalStaff){
		DecimalFormat dfm = new DecimalFormat("###.##");
		return Double.parseDouble(dfm.format((amountStaffbyType*100.00)/totalStaff));
	}
	
}
